package dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import trip.Company;
import trip.HibernateUtil;
import trip.Person;
import trip.Trip;

public class PersonDAOImpl extends GenericDaoImpl<Person> implements PersonDAO {
	public PersonDAOImpl(Class<Person> type) {
		super(type);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Person> getByInfo(String fN, String lN, String mN, String pN, String email, Integer min, Integer max, Company company, Integer trip) {
		Session session = null;
		List<Person> result = new ArrayList<Person>();
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			if (fN == null && lN == null && mN == null && pN == null && email == null 
				&& min == -1 && max == -1 && company == null && trip == null)
				return result;
			String query_str = "select p from Person p where ";
			if (fN != null)
				query_str += "p.first_name like :fn and ";
			if (lN != null)
				query_str += "p.last_name like :ln and ";
			if (mN != null)
				query_str += "p.middle_name like :mn and ";
			if (pN != null)
				query_str += "p.phone_number like :pn and ";
			if (email != null)
				query_str += "p.email like :email and ";
			if (min != -1)
				query_str += "TIMESTAMPDIFF(YEAR, p.birth, curdate()) >= :min and ";
			if (max != -1)
				query_str += "TIMESTAMPDIFF(YEAR, p.birth, curdate()) <= :max and ";
			query_str += "1 = 1";
			Query query = session.createQuery(query_str);
			if (fN != null)
				query.setString("fn", "%" + fN + "%");
			if (lN != null)
				query.setString("ln", "%" + lN + "%");
			if (mN != null)
				query.setString("mn", "%" + mN + "%");
			if (pN != null)
				query.setString("pn", "%" + pN + "%");
			if (email != null)
				query.setString("email", "%" + email + "%");
			if (min != -1)
				query.setInteger("min", min);
			if (max != -1)
				query.setInteger("max", max);
			if (query.list().size() == 0)
				return result;
			if (trip == null && company == null)
				return query.list();
			String tc_str = "select distinct pt.person from Person_trip pt where ";
			if (trip != null)
				tc_str += "pt.trip.number = :tnum and ";
			if (company != null)
				tc_str += "pt.trip.company = :cid and ";
			tc_str += "1 = 1";
			Query tc_query = session.createQuery(tc_str);
			if (trip != null)
				tc_query.setLong("tnum", trip);
			if (company != null)
				tc_query.setLong("cid", company.getId());
			if (tc_query.list().size() == 0)
				return result;
			System.out.println(tc_query.list());
			Query fin_query = session.createQuery("select p from Person p where p in (:iid) and p in (:tcid)");
			fin_query.setParameterList("iid", query.list());
			fin_query.setParameterList("tcid", tc_query.list());
			result = fin_query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Person> getByCompany(Company company) {
		Session session = null;
		List<Person> result = new ArrayList<Person>();
		if (company == null)
			return result;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query query = session.createQuery("select distinct pt.person "
					+ "from Person_trip pt "
					+ "where pt.trip.company = :id");
			query.setLong("id", company.getId());
			result = query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Person> getByTrip(Trip trip) {
		Session session = null;
		List<Person> result = new ArrayList<Person>();
		if (trip == null)
			return result;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query query = session.createQuery("select distinct pt.person "
					+ "from Person_trip pt "
					+ "where pt.trip = :id");
			query.setLong("id", trip.getId());
			result = query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}
	
	@Override
	public void create(Person person) {
		Session session = null;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			if (person != null) {
				if (person.getMiddle_name() == null)
					person.setMiddle_name("");
				if (person.getPhone_number() == null)
					person.setPhone_number("");
				if (person.getEmail() == null)
					person.setEmail("");
			}
			session.save(person);
			session.getTransaction().commit();
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}

	@Override
	public Person getByEmail(String email) {
		Session session = null;
		Person result = null;
		if (email == null)
			return result;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query query = session.createQuery("select distinct p from Person p "
					+ "where p.email = :email").setString("email", email).setMaxResults(1);
			if (!query.list().isEmpty())
				result = (Person) query.list().get(0);
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}
}
